package com.threeti.mecool.core.domain.model.project;

import com.threeti.mecool.core.domain.model.Config;
import com.threeti.mecool.core.domain.model.ConfigKey;
import com.threeti.mecool.core.domain.model.Hint;
import com.threeti.mecool.core.domain.model.HintKey;
import com.threeti.mecool.core.domain.model.acl.User;
import com.threeti.mecool.core.domain.model.dto.AssignableProjectStoreDto;
import com.threeti.mecool.core.domain.model.dto.AssignableProjectStoreWithSellingUploadStatusDto;
import com.threeti.mecool.core.exception.PreSaveValidateException;
import com.threeti.mecool.core.infrastructure.persistence.acl.UserRepository;
import com.threeti.mecool.core.infrastructure.persistence.project.*;
import org.joda.time.DateTime;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.*;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.text.SimpleDateFormat;
import java.util.*;

@Service
public class SellingUpload {
    //<editor-fold desc="fields">
    @PersistenceContext EntityManager entityManager;
    @Autowired private ProjectStoreRepository projectStoreRepository;
    @Autowired private ProjectSellingUploadRepository projectSellingUploadRepository;
    @Autowired private ProjectStoreSellingTargetRepository targetRepository;
    @Autowired private ProjectStoreSellingTargetValueRepository targetValueRepository;
    @Autowired private ProjectStoreSellingValueRepository valueRepository;
    @Autowired private UserRepository userRepository;
    @Autowired private Hint hint;
    @Autowired private Config config;
    //</editor-fold>

    public List<ProjectStoreSellingTargetValue> getTargetValues(long projectStoreId, int year) {
        int yearFrom = year * 100 + 1;
        int yearTo = year * 100 + 12;
        return targetValueRepository.findBySellingTarget_ProjectStore_IdAndYearMonthBetween(projectStoreId, yearFrom, yearTo, new Sort("yearMonth"));
    }

    public int getSummary(long projectStoreId, int year, int month) {
        Pageable pageable = new PageRequest(0, 1, Sort.Direction.DESC, "yearMonth");
        List<ProjectStoreSellingTargetValue> list = targetValueRepository.findBySellingTarget_ProjectStore_IdAndYearMonthLessThan(
                projectStoreId, year * 100 + month + 1, pageable);
        return list.size() != 0 ? list.get(0).getSummary() : 0;
    }

    /**
     *
     * @param projectStoreId id
     * @param monthValues {1:1000, 12:2000}
     */
    @Transactional
    public void saveTargetValues(long projectStoreId, int year, Map<Integer, Integer> monthValues) {
        List<ProjectStoreSellingTargetValue> list = getTargetValues(projectStoreId, year);
        Map<Integer, Integer> inserts = new HashMap<Integer, Integer>(monthValues);
        Set<ProjectStoreSellingTargetValue> removes = new HashSet<ProjectStoreSellingTargetValue>();
        for (ProjectStoreSellingTargetValue value : list) {
            Integer summary = inserts.get(value.getMonth());
            if (summary != null) {
                value.setSummary(summary);
                inserts.remove(value.getMonth());
            } else {
                removes.add(value);
            }
        }
        ProjectStore projectStore = projectStoreRepository.findOne(projectStoreId);
        ProjectStoreSellingTarget projectStoreSellingTarget = projectStore.getSellingTarget();
        if (projectStoreSellingTarget == null) {
            projectStoreSellingTarget = new ProjectStoreSellingTarget();
            projectStoreSellingTarget.setProjectStore(projectStore);
        }
        for (Map.Entry<Integer, Integer> monthValue : inserts.entrySet()) {
            projectStoreSellingTarget.add(year, monthValue.getKey(), monthValue.getValue());
        }
        projectStoreSellingTarget.getValues().removeAll(removes);
        targetRepository.save(projectStoreSellingTarget);
        targetValueRepository.delete(removes);
    }

    /**
     *
     * @param loginName user.loginName
     * @param projectStoreId projectStore.id
     * @param date sellDate
     * @param values 'code': {'summary':10, 'amount':20, 'price':30}; code=categoryCode/brandCode/skuCode
     */
    @Transactional
    public void saveUploadValues(String loginName, long projectStoreId, Date date, Map<String, Map<String, Number>> values) {
        DateTime sellDate = new DateTime(date).withTime(0, 0, 0, 0);
        //<editor-fold desc="validate">
        { // sell date
            int[] timePeriod;
            try {
                timePeriod = config.getTimePeriod(ConfigKey.SU_DAY_PERIOD);
            } catch (Exception e) {
                throw new PreSaveValidateException(e.getMessage());
            }
            int timeFrom = timePeriod[0];
            int timeTo = timePeriod[1];
            DateTime now = new DateTime();
            DateTime today = now.withTime(0, 0, 0, 0);
            int timeNow = now.getHourOfDay() * 100 + now.getMinuteOfHour();
            boolean allowUpload = false;
            if (timeFrom < timeTo) {
                allowUpload = sellDate.equals(today) && timeFrom <= timeNow && timeNow <= timeTo;
            } else {
                if (sellDate.equals(today)) {
                    allowUpload = timeFrom <= timeNow;
                } else if (sellDate.equals(today.minusDays(1))) {
                    allowUpload = timeNow <= timeTo;
                }
            }
            if (!allowUpload) {
                String msg = "销量上报时间为当天%02d:%02d至%s%02d:%02d";
                if (timeFrom < timeTo) {
                    msg = String.format(msg, timeFrom / 100, timeFrom % 100, "", timeTo / 100, timeTo % 100);
                } else {
                    msg = String.format(msg, timeFrom / 100, timeFrom % 100, "第二天", timeTo / 100, timeTo % 100);
                }
                throw new PreSaveValidateException(msg);
            }
        }
        if (0 != valueRepository.findByProjectStore_IdAndSellDate(projectStoreId, date, new PageRequest(0, 1)).size()) {
            throw new PreSaveValidateException(hint.get(HintKey.SU_DUPLICATED));
        }
        ProjectStore projectStore = projectStoreRepository.findOne(projectStoreId);
        if (projectStore == null) {
            throw new PreSaveValidateException("门店ID错误，projectStoreId");
        }
        if (null == projectStore.getProject().getSellingUpload()) {
            throw new PreSaveValidateException("该项目没有配置销量上报");
        }
        //</editor-fold>
        ProjectProductCollectionType uploadType = projectStore.getProject().getSellingUpload().getUploadType();
        String amountUnit = projectStore.getProject().getSellingUpload().getUploadAmountUnit();
        User user = userRepository.findByloginName(loginName);
        List<ProjectStoreSellingValue> projectStoreSellingValues = new LinkedList<ProjectStoreSellingValue>();
        Date updatedAt = new Date();
        Date aSellDate = sellDate.toDate();
        for (Map.Entry<String, Map<String, Number>> codes : values.entrySet()) {
            Map<String, Number> codeValues = codes.getValue();
            ProjectStoreSellingValue sellingValue = new ProjectStoreSellingValue.Builder(projectStore, uploadType, updatedAt, user, aSellDate)
                    .summary(codeValues.get("summary")).price(codeValues.get("price")).amount(codeValues.get("amount"))
                    .amountUnit(amountUnit).code(codes.getKey())
                    .build();
            projectStoreSellingValues.add(sellingValue);
        }
        valueRepository.save(projectStoreSellingValues);
    }

    public DateTime calculateSellDate() {
        DateTime now = new DateTime();
        DateTime sellDate = now.withTime(0, 0, 0, 0);
        int[] timePeriod;
        try {
            timePeriod = config.getTimePeriod(ConfigKey.SU_DAY_PERIOD);
        } catch (Exception e) {
            throw new PreSaveValidateException(e.getMessage());
        }
        int timeFrom = timePeriod[0];
        int timeTo = timePeriod[1];
        int timeNow = now.getHourOfDay() * 100 + now.getMinuteOfHour();
        if (timeFrom < timeTo) {
            if (timeTo < timeNow) {
                sellDate = sellDate.plusDays(1);
            }
        } else {
            if (timeNow <= timeTo) {
                sellDate = sellDate.minus(1);
            }
        }
        return sellDate;
    }

    public List<AssignableProjectStoreWithSellingUploadStatusDto> fillUploadStatus(Date sellDate, List<AssignableProjectStoreDto> stores) {
        List<AssignableProjectStoreWithSellingUploadStatusDto> list = new ArrayList<AssignableProjectStoreWithSellingUploadStatusDto>(stores.size());
        Map<Long, AssignableProjectStoreWithSellingUploadStatusDto> storeMap = new HashMap<Long, AssignableProjectStoreWithSellingUploadStatusDto>();
        StringBuilder storeId = new StringBuilder("(-1");
        for (AssignableProjectStoreDto store : stores) {
            AssignableProjectStoreWithSellingUploadStatusDto obj = new AssignableProjectStoreWithSellingUploadStatusDto(store);
            list.add(obj);
            storeId.append(",").append(obj.getId());
            storeMap.put(obj.getId(), obj);
        }
        storeId.append(")");
        StringBuilder sql = new StringBuilder();
        sql.append("select project_store from DM_PROJECT_STORE_SV\n where project_store in ").append(storeId.toString());
        sql.append("\n and sell_date=to_date('").append(new SimpleDateFormat("yyyy-MM-dd").format(sellDate))
                .append("','yyyy-mm-dd')\n group by project_store\n");
        Query query = entityManager.createNativeQuery(sql.toString());
        @SuppressWarnings("unchecked")
        List<Object> dataset = query.getResultList();
        for (Object data : dataset) {
            AssignableProjectStoreWithSellingUploadStatusDto store = storeMap.get(((Number) data).longValue());
            if (store != null) {
                store.setUploaded(1);
            }
        }
        return list;
    }

    public boolean isSellingUploaded(long projectStoreId, Date date) {
        String sql = "select count(*) from DM_PROJECT_STORE_SV where project_store=" + projectStoreId +
                " and sell_date=to_date('" + new SimpleDateFormat("yyyy-MM-dd").format(date) +
                "','yyyy-mm-dd')";
        return 0 != ((Number) entityManager.createNativeQuery(sql).getSingleResult()).intValue();
    }

    public double getMonthSummary(long projectStoreId, int year, int month, DateTime beforeDate) {
        StringBuilder sql = new StringBuilder();
        sql.append("\n select nvl(sum(summary),0)");
        sql.append("\n from dm_project_store_sv");
        sql.append("\n where project_store=").append(projectStoreId);
        sql.append("\n and sell_date>=to_date('").append(year).append("-").append(month).append("-1','yyyy-mm-dd')");
        if (beforeDate != null) {
            sql.append("\n and sell_date<to_date('").append(beforeDate.toString("yyyy-MM-dd")).append("','yyyy-mm-dd')");
        }
        return ((Number) entityManager.createNativeQuery(sql.toString()).getSingleResult()).doubleValue();
    }

    @SuppressWarnings("unchecked")
    public List<Map<String, Object>> getUploadList(ProjectStore projectStore, Date date) {
        DateTime sellDate = new DateTime(date);
        List<Map<String, Object>> list = new LinkedList<Map<String, Object>>();
        ProjectSellingUpload projectSellingUpload = projectSellingUploadRepository.findByProject_Id(projectStore.getProject().getId());
        boolean withAmount = projectSellingUpload.getUploadAmount() == 1;
        String amountUnit = projectSellingUpload.getUploadAmountUnit();
        boolean withPrice = projectSellingUpload.getUploadPrice() == 1;
        boolean isUploaded = isSellingUploaded(projectStore.getId(), date);
        ProjectProductCollectionType uploadType = projectSellingUpload.getUploadType();
        Map<String, Map<String, Object>> codeMap = new HashMap<String, Map<String, Object>>();
        if (isUploaded) {
            //<editor-fold desc="uploaded data">
            String s = "select upload_type from DM_PROJECT_STORE_SV where project_store=" + projectStore.getId() +
                    " and sell_date=to_date('" + sellDate.toString("yyyy-MM-dd") + "','yyyy-mm-dd') and rownum=1";
            String uploadTypeString = (String) entityManager.createNativeQuery(s).getSingleResult();
            uploadType = ProjectProductCollectionType.valueOf(uploadTypeString);
            StringBuilder sql = new StringBuilder();
            sql.append("\n select v.summary, v.price, v.amount, v.amount_unit,");
            switch (uploadType) {
                case CATEGORY: sql.append("\n t.cat_code code, t.memo name"); break;
                case BRAND:    sql.append("\n t.brand_code code, t.brand_name name"); break;
                case SKU:      sql.append("\n t.article_code code, t.article_sku name"); break;
            }
            sql.append("\n from DM_PROJECT_STORE_SV v");
            switch (uploadType) {
                case CATEGORY: sql.append("\n join MD_CAT t on t.cat_code=v.category_code"); break;
                case BRAND:    sql.append("\n join MD_BRAND t on t.brand_code=v.brand_code"); break;
                case SKU:      sql.append("\n join MD_ARTICLE t on t.article_code=v.sku_code"); break;
            }
            sql.append("\n where v.project_store=").append(projectStore.getId());
            sql.append("\n and v.sell_date=to_date('").append(sellDate.toString("yyyy-MM-dd")).append("','yyyy-mm-dd')");
            sql.append("\n order by name");
            List<Object[]> dataset = entityManager.createNativeQuery(sql.toString()).getResultList();
            for (Object[] data : dataset) {
                Map<String, Object> map = new HashMap<String, Object>();
                list.add(map);
                codeMap.put(String.valueOf(data[4]), map);
                map.put("c", data[4]);
                map.put("n", data[5]);
                map.put("t", 0);
                map.put("s", data[0]);
                if (withPrice) {
                    map.put("p", data[1]);
                }
                if (withAmount) {
                    map.put("a", data[2]);
                    map.put("u", data[3] != null ? data[3] : "");
                }
            }
            //</editor-fold>
        } else {
            //<editor-fold desc="new form">
            StringBuilder sql = new StringBuilder();
            sql.append("\n select ");
            switch (uploadType) {
                case CATEGORY: sql.append("t.cat_code code, t.memo name"); break;
                case BRAND:    sql.append("t.brand_code code, t.brand_name name"); break;
                case SKU:      sql.append("t.article_code code, t.article_sku name"); break;
            }
            sql.append("\n from DM_PROJECT_SU u");
            sql.append("\n join DM_PROJECT_SU_CODE c on c.project_selling_upload=u.id");
            switch (uploadType) {
                case CATEGORY: sql.append("\n join MD_CAT t on t.cat_code=c.codes"); break;
                case BRAND:    sql.append("\n join MD_BRAND t on t.brand_code=c.codes"); break;
                case SKU:      sql.append("\n join MD_ARTICLE t on t.article_code=c.codes"); break;
            }
            sql.append("\n where u.project=").append(projectStore.getProject().getId());
            sql.append("\n order by name");
            List<Object[]> dataset = entityManager.createNativeQuery(sql.toString()).getResultList();
            for (Object[] data : dataset) {
                Map<String, Object> map = new HashMap<String, Object>();
                list.add(map);
                codeMap.put(String.valueOf(data[0]), map);
                map.put("c", data[0]);
                map.put("n", data[1]);
                map.put("t", 0);
                map.put("s", 0);
                if (withPrice) {
                    map.put("p", 0);
                }
                if (withAmount) {
                    map.put("a", 0);
                    map.put("u", amountUnit);
                }
            }
            //</editor-fold>
        }
        //<editor-fold desc="update month total">
        StringBuilder sql = new StringBuilder();
        sql.append("\n select ");
        switch (uploadType) {
            case CATEGORY: sql.append("category_code"); break;
            case BRAND:    sql.append("brand_code"); break;
            case SKU:      sql.append("sku_code"); break;
        }
        sql.append(", nvl(sum(summary),0)");
        sql.append("\n from DM_PROJECT_STORE_SV");
        sql.append("\n where project_store=").append(projectStore.getId());
        sql.append("\n and sell_date>=to_date('").append(sellDate.withDayOfMonth(1).toString("yyyy-MM-dd")).append("','yyyy-mm-dd')");
        sql.append("\n and sell_date<to_date('").append(sellDate.toString("yyyy-MM-dd")).append("','yyyy-mm-dd')");
        sql.append("\n and upload_type='").append(uploadType).append("'");
        sql.append("\n group by ");
        switch (uploadType) {
            case CATEGORY: sql.append("category_code"); break;
            case BRAND:    sql.append("brand_code"); break;
            case SKU:      sql.append("sku_code"); break;
        }
        List<Object[]> dataset = entityManager.createNativeQuery(sql.toString()).getResultList();
        for (Object[] data : dataset) {
            String code = String.valueOf(data[0]);
            Number summary = (Number) data[1];
            Map<String, Object> map = codeMap.get(code);
            if (map != null) {
                map.put("t", summary);
            }
        }
        //</editor-fold>
        return list;
    }

    @SuppressWarnings("unchecked")
    public Page findMySellingUpload(Pageable pageable, User user,
                                    String projectCode, String projectName, String channelCode, String channelName,
                                    String adCode, String channelType, String channelSystem,
                                    Date dateFrom, Date dateTo) {
        //<editor-fold desc="select">
        final String fields ="\n select c.channel_province_ad_name province," + //0
                "\n c.channel_city_ad_name city," +
                "\n p.code projectCode," +
                "\n p.name projectName," +
                "\n sys.pos_sys_type_name systemType," +
                "\n c.channel_code channelCode," + //5
                "\n c.channel_name channelName," +
                "\n cat.memo category," +
                "\n brand.brand_name brand," +
                "\n sku.article_sku sku," +
                "\n v.price price," + //10
                "\n v.amount," +
                "\n unit.text amountUnit," +
                "\n v.summary summary";
        //</editor-fold>
        //<editor-fold desc="from">
        final String from = "\n from dm_project_store_sv v" +
                "\n join dm_project_store s on s.id=v.project_store" +
                "\n join dm_project p on p.id=s.project" +
                "\n join dm_channel_sync c on c.id=s.channel" +
                "\n join md_pos_sys_type sys on sys.pos_sys_type_code=c.channel_sys_cat" +
                "\n left join md_cat cat on cat.cat_code=v.category_code" +
                "\n left join md_brand brand on brand.brand_code=v.brand_code" +
                "\n left join md_article sku on sku.article_code=v.sku_code" +
                "\n left join dm_common_kvt unit on unit.val=v.amount_unit";
        //</editor-fold>
        //<editor-fold desc="where">
        StringBuilder where = new StringBuilder(200);
        Map<String, Object> paramMap = new HashMap<String, Object>();
        where.append("\n where v.updated_by=").append(user.getId());
        if (notEmpty(projectCode)) {
            where.append("\n and p.code like :projectCode");
            paramMap.put("projectCode", "%" + projectCode + "%");
        }
        if (notEmpty(projectName)) {
            where.append("\n and p.name like :projectName");
            paramMap.put("projectName", "%" + projectName + "%");
        }
        if (notEmpty(channelCode)) {
            where.append("\n and c.channel_code like :channelCode");
            paramMap.put("channelCode", "%" + channelCode + "%");
        }
        if (notEmpty(channelName)) {
            where.append("\n and c.channel_name like :channelName");
            paramMap.put("channelName", "%" + channelName + "%");
        }
        if (notEmpty(adCode)) {
            where.append("\n and c.channel_ad_code like :adCode");
            paramMap.put("adCode", adCode + "%");
        }
        if (notEmpty(channelType)) {
            where.append("\n and c.type=:channelType");
            paramMap.put("channelType", channelType);
        }
        if (notEmpty(channelSystem)) {
            where.append("\n and c.channel_sys_cat=:channelSystem");
            paramMap.put("channelSystem", channelSystem);
        }
        if (notEmpty(channelCode)) {
            where.append("\n and c.channel_code like :channelCode");
            paramMap.put("channelCode", "%" + channelCode + "%");
        }
        if (notEmpty(dateFrom)) {
            where.append("\n and v.sell_date>=:dateFrom");
            paramMap.put("dateFrom", dateFrom);
        }
        if (notEmpty(dateTo)) {
            where.append("\n and v.sell_date<=:dateTo");
            paramMap.put("dateTo", dateTo);
        }
        //</editor-fold>
        final String order = "\n order by projectName, province, city, systemType, channelName, category, brand, sku";
        StringBuilder count = new StringBuilder(200);
        count.append("select count(*)").append(from).append(where);
        Query query = entityManager.createNativeQuery(count.toString());
        for (Map.Entry<String, Object> entry : paramMap.entrySet()) {
            query.setParameter(entry.getKey(), entry.getValue());
        }
        long total = ((Number) query.getSingleResult()).longValue();
        StringBuilder select = new StringBuilder(200);
        select.append("select * from (select row_.*, rownum ROWNUM_ from(");
        select.append(fields).append(from).append(where).append(order);
        select.append(") row_ where rownum<=:row_to) where ROWNUM_>:row_from");
        List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>(pageable.getPageSize());
        query = entityManager.createNativeQuery(select.toString());
        for (Map.Entry<String, Object> entry : paramMap.entrySet()) {
            query.setParameter(entry.getKey(), entry.getValue());
        }
        query.setParameter("row_from", pageable.getOffset());
        query.setParameter("row_to", pageable.getOffset() + pageable.getPageSize());
        List<Object[]> dataset = query.getResultList();
        for (Object[] val : dataset) {
            Map<String, Object> map = new HashMap<String, Object>();
            resultList.add(map);
            map.put("province", val[0]);
            map.put("city", val[1]);
            map.put("projectCode", val[2]);
            map.put("projectName", val[3]);
            map.put("systemType", val[4]);
            map.put("channelCode", val[5]);
            map.put("channelName", val[6]);
            map.put("category", val[7]);
            map.put("brand", val[8]);
            map.put("sku", val[9]);
            map.put("price", val[10]);
            map.put("amount", val[11]);
            map.put("amountUnit", val[12]);
            map.put("summary", val[13]);
        }
        return new PageImpl(resultList, pageable, total);
    }

    @SuppressWarnings("unchecked")
    public Page findSellingUpload(Pageable pageable, User user,
                                    String projectCode, String projectName, String channelCode, String channelName,
                                    String adCode, String channelType, String channelSystem,
                                    Date dateFrom, Date dateTo) {
        //<editor-fold desc="select">
        final String fields ="\n select c.channel_province_ad_name province," + //0
                "\n c.channel_city_ad_name city," +
                "\n p.code projectCode," +
                "\n p.name projectName," +
                "\n sys.pos_sys_type_name systemType," +
                "\n c.channel_code channelCode," + //5
                "\n c.channel_name channelName," +
                "\n cat.memo category," +
                "\n brand.brand_name brand," +
                "\n sku.article_sku sku," +
                "\n v.price price," + //10
                "\n v.amount," +
                "\n unit.text amountUnit," +
                "\n v.summary summary";
        //</editor-fold>
        //<editor-fold desc="from">
        final String from = "\n from dm_project_store_sv v" +
                "\n join dm_project_store s on s.id=v.project_store" +
                "\n join dm_project p on p.id=s.project" +
                "\n join dm_channel_sync c on c.id=s.channel" +
                "\n join md_pos_sys_type sys on sys.pos_sys_type_code=c.channel_sys_cat" +
                "\n left join md_cat cat on cat.cat_code=v.category_code" +
                "\n left join md_brand brand on brand.brand_code=v.brand_code" +
                "\n left join md_article sku on sku.article_code=v.sku_code" +
                "\n left join dm_common_kvt unit on unit.val=v.amount_unit";
        //</editor-fold>
        //<editor-fold desc="where">
        StringBuilder where = new StringBuilder(500);
        Map<String, Object> paramMap = new HashMap<String, Object>();
        where.append("\n where 1=1");
        if (user.getPermissionRole().isAdvanced() || user.getPermissionRole().getUseADFilter().equals(1)) { // advance user, restrict to admin_div codes
            where.append("\n and c.channel_ad_code in(select managed_admindiv_adcode from dm_permrole_admin_div_adcode where permission_role=")
                    .append(user.getPermissionRole().getId()).append(")");
        }
        if (!user.getPermissionRole().isAdvanced()) { // normal user, project restriction
            Long empId = user.getEmployee().getEmpId();
            List<Number> projectIdList = (List<Number>) entityManager
                    .createNativeQuery("select distinct project from dm_project_employee where emp_id=" + empId)
                    .getResultList();
            where.append("\n and p.id in (-9999");
            for (Number val : projectIdList) {
                where.append(",").append(val);
            }
            where.append(")");
        }
        if (notEmpty(projectCode)) {
            where.append("\n and p.code like :projectCode");
            paramMap.put("projectCode", "%" + projectCode + "%");
        }
        if (notEmpty(projectName)) {
            where.append("\n and p.name like :projectName");
            paramMap.put("projectName", "%" + projectName + "%");
        }
        if (notEmpty(channelCode)) {
            where.append("\n and c.channel_code like :channelCode");
            paramMap.put("channelCode", "%" + channelCode + "%");
        }
        if (notEmpty(channelName)) {
            where.append("\n and c.channel_name like :channelName");
            paramMap.put("channelName", "%" + channelName + "%");
        }
        if (notEmpty(adCode)) {
            where.append("\n and c.channel_ad_code like :adCode");
            paramMap.put("adCode", adCode + "%");
        }
        if (notEmpty(channelType)) {
            where.append("\n and c.type=:channelType");
            paramMap.put("channelType", channelType);
        }
        if (notEmpty(channelSystem)) {
            where.append("\n and c.channel_sys_cat=:channelSystem");
            paramMap.put("channelSystem", channelSystem);
        }
        if (notEmpty(channelCode)) {
            where.append("\n and c.channel_code like :channelCode");
            paramMap.put("channelCode", "%" + channelCode + "%");
        }
        if (notEmpty(dateFrom)) {
            where.append("\n and v.sell_date>=:dateFrom");
            paramMap.put("dateFrom", dateFrom);
        }
        if (notEmpty(dateTo)) {
            where.append("\n and v.sell_date<=:dateTo");
            paramMap.put("dateTo", dateTo);
        }
        //</editor-fold>
        final String order = "\n order by projectName, province, city, systemType, channelName, category, brand, sku";
        StringBuilder count = new StringBuilder(200);
        count.append("select count(*)").append(from).append(where);
        Query query = entityManager.createNativeQuery(count.toString());
        for (Map.Entry<String, Object> entry : paramMap.entrySet()) {
            query.setParameter(entry.getKey(), entry.getValue());
        }
        long total = ((Number) query.getSingleResult()).longValue();
        StringBuilder select = new StringBuilder(200);
        select.append("select * from (select row_.*, rownum ROWNUM_ from(");
        select.append(fields).append(from).append(where).append(order);
        select.append(") row_ where rownum<=:row_to) where ROWNUM_>:row_from");
        List<Map<String, Object>> resultList = new ArrayList<Map<String, Object>>(pageable.getPageSize());
        query = entityManager.createNativeQuery(select.toString());
        for (Map.Entry<String, Object> entry : paramMap.entrySet()) {
            query.setParameter(entry.getKey(), entry.getValue());
        }
        query.setParameter("row_from", pageable.getOffset());
        query.setParameter("row_to", pageable.getOffset() + pageable.getPageSize());
        List<Object[]> dataset = query.getResultList();
        for (Object[] val : dataset) {
            Map<String, Object> map = new HashMap<String, Object>();
            resultList.add(map);
            map.put("province", val[0]);
            map.put("city", val[1]);
            map.put("projectCode", val[2]);
            map.put("projectName", val[3]);
            map.put("systemType", val[4]);
            map.put("channelCode", val[5]);
            map.put("channelName", val[6]);
            map.put("category", val[7]);
            map.put("brand", val[8]);
            map.put("sku", val[9]);
            map.put("price", val[10]);
            map.put("amount", val[11]);
            map.put("amountUnit", val[12]);
            map.put("summary", val[13]);
        }
        return new PageImpl(resultList, pageable, total);
    }

    private boolean notEmpty(Object obj) {
        return obj != null && !obj.toString().trim().isEmpty();
    }

}
